﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Windows.Forms;

namespace SP.OpenSource.CDC.Monitor
{
    public partial class CDCcapturedData : UserControl
    {
        String m_sConnectedConfigID = String.Empty;
        
        
        String m_sConnectionString = String.Empty;
        String m_sStateConnectionString = String.Empty;
        String m_sConnectedDBName = String.Empty;
        String m_sConnectedServerName = String.Empty;
        String m_sEncryptionType = String.Empty;

        DataTable m_dtCDCEnabledTableDetails = new DataTable("CDCEnabledTables");
        DataTable m_dtCDCTableColumns = new DataTable("CDCTableColumns");

        String m_sExePath = String.Empty;
        DataSet m_dsConfigurations = new DataSet();

        String m_sErrorMessage = String.Empty;
        String m_sFormTitle = "MonitorCDC - Captured Data View";
        BLL.CDCManagement m_oCDCManagement = new BLL.CDCManagement();

        String m_sEndLSN = String.Empty;
        String m_sStartLSN = String.Empty;
        String m_sStartTime = String.Empty;
        bool m_bStartEndLSNSame = false;

        public CDCcapturedData()
        {
            InitializeComponent();
        }
        public CDCcapturedData(String sConnectionString, String sConnectedDBName, String sConnectedServerName, String sConnectedConfigID, String sEncryptionType)
        {
            InitializeComponent();
            m_sConnectedConfigID = sConnectedConfigID;
            m_sConnectionString = sConnectionString;
            m_sConnectedDBName = sConnectedDBName;
            m_sConnectedServerName = sConnectedServerName;
            m_sEncryptionType = sEncryptionType;
        }

        private void CDCcapturedData_Load(object sender, EventArgs e)
        {
            try
            {
                m_sExePath = System.Windows.Forms.Application.StartupPath.ToString();
                //LoadCDCCaptureInfo();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception raised, while processing request. Message: " + ex.Message, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }


        
        public void LoadCDCCaptureInfo()
        {
            try
            {
                
                m_dtCDCEnabledTableDetails = m_oCDCManagement.GetCDCEnabledTables(m_sConnectionString, ref m_sErrorMessage);

                DataTable dtTables = m_dtCDCEnabledTableDetails.DefaultView.ToTable(true, new String[] { "source_schema","source_table","table_fullname" });

                cmbCDCEnabledTables.DataSource = dtTables;
                cmbCDCEnabledTables.DisplayMember = "table_fullname";
                cmbCDCEnabledTables.ValueMember = "table_fullname";

                cmbCDCEnabledTables.SelectedIndex = -1;
                cmbCaptureInstance.SelectedIndex = -1;

            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception raised, while processing request. Message: " + ex.Message, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void TableSelectionChanged()
        {
            try
            {
                if (cmbCDCEnabledTables.Text == "System.Data.DataRowView")
                    return;
                if (cmbCDCEnabledTables.Text.Length > 0)
                {
                    //Clear cmbCaptureInstance
                    cmbCaptureInstance.Items.Clear();

                    DataTable dtCaptureInstance = m_dtCDCEnabledTableDetails.DefaultView.ToTable(true, new String[] { "source_schema", "source_table", "table_fullname", "capture_instance" });

                    DataRow[] drSelected = dtCaptureInstance.Select("table_fullname ='" + cmbCDCEnabledTables.Text + "'");

                    foreach (DataRow drNew in drSelected)
                    {
                        cmbCaptureInstance.Items.Add(drNew["capture_instance"].ToString());
                    }

                    if (cmbCaptureInstance.Items.Count == 1)
                        cmbCaptureInstance.SelectedIndex = 0;
                    else 
                        cmbCaptureInstance.SelectedIndex = -1;

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception raised, while processing request. Message: " + ex.Message, m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private void cmbCDCEnabledTables_SelectionChangeCommitted(object sender, EventArgs e)
        {
           // TableSelectionChanged();
        }

        private void btnGetCapturedData_Click(object sender, EventArgs e)
        {
            try
            {
                if (cmbCDCEnabledTables.Text.Length <= 0)
                {
                    MessageBox.Show("Select a table.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                if (cmbCaptureInstance.Text.Length <= 0)
                {
                    MessageBox.Show("Select a capture instance.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
             
                DataRow[] dtSelected = m_dtCDCEnabledTableDetails.Select("table_fullname='" + cmbCDCEnabledTables.Text + "' AND capture_instance='" + cmbCaptureInstance.Text + "'");

                if(dtSelected.Length <= 0)
                {
                    
                    MessageBox.Show("There was an error while processing request.", m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                lblCapturedColumns.Text = dtSelected[0]["captured_column_list"].ToString();

                String sCapInstance = cmbCaptureInstance.Text.ToString();
                String sCapColumnList = lblCapturedColumns.Text;
                
                String sErrorMessage = String.Empty;

                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
                DbConnection connection = factory.CreateConnection();
                connection.ConnectionString = m_sConnectionString;

                DbCommand command = factory.CreateCommand();

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                DbConnection dbcCDCStateconnection = factory.CreateConnection();

                String sQueryPreview = String.Empty;
                String sGetChangesFilter = "all update old";

                
                String sQueryCDCData = String.Empty;

                sQueryCDCData = "select [__$operation],";
                sQueryCDCData += " case when [__$operation] = 1 then 'Delete' ";
                sQueryCDCData += " when [__$operation] = 2 then 'Insert' ";
                sQueryCDCData += " when [__$operation] = 3 then 'Before Update' ";
                sQueryCDCData += " else 'Update' end as [Operation],";
                sQueryCDCData += lblCapturedColumns.Text;
                sQueryCDCData += " from [cdc].[fn_cdc_get_all_changes_";
                sQueryCDCData += sCapInstance;
                sQueryCDCData += "] (sys.fn_cdc_get_min_lsn(N'";
                sQueryCDCData += sCapInstance + "'),";
                sQueryCDCData += " sys.fn_cdc_get_max_lsn(),	N'";
                sQueryCDCData += sGetChangesFilter + "')";
                
                //Execute the query
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = sQueryCDCData;

                DataTable dtCapturedData = new DataTable();
                if (m_bStartEndLSNSame == false)
                {
                    DbDataAdapter adapter = factory.CreateDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(dtCapturedData);

                    if (connection.State == ConnectionState.Open)
                        connection.Close();


                    grdCapturedData.DataSource = dtCapturedData;
                    grdCapturedData.Visible = true;
                    lblStatusCDCData.Visible = false;
                }
                else
                {
                    grdCapturedData.Visible = false;
                    lblStatusCDCData.Visible = true;
                }

            }
            catch (Exception ex)
            {
                grdCapturedData.Visible = false;
                lblStatusCDCData.Visible = true;
                
                MessageBox.Show("Exception raised, while fetching captured information. " , m_sFormTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void cmbCDCEnabledTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbCDCEnabledTables.Text.Length > 0)
            {
                lblCaptureInstance.Enabled = true;
                cmbCaptureInstance.Enabled = true;
            }
            else
            {
                lblCaptureInstance.Enabled = false;
                cmbCaptureInstance.Enabled = false;
            }
            TableSelectionChanged();
        }

        
    }
}
